package engineer.serverapp.classes;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;



import engineer.serverapp.tools.Connector;

public class MessageToDatabase {

	
	public String sendToDatabase(int machineID) {
		try {
			Connection connection = new Connector().getConnection();
			Statement selectStatement = connection.createStatement();
			String query = "select machines.name, machines.address, machines_trademarks.current_amount, dictionary_trademarks.name from automats.machines   join automats.machines_trademarks on machines.id=machines_trademarks.machine_id  join automats.dictionary_trademarks on dictionary_trademarks.id=machines_trademarks.trademark_id where machines_trademarks.current_amount<10 and machines.id="
					+ machineID;
			ResultSet rs = selectStatement.executeQuery(query);
			Statement selectStatement2 = connection.createStatement();
			
			while(rs.next()){
				String machineName = rs.getString("machines.name");
				String machineAddress = rs.getString("machines.address");
				int currentAmount = rs.getInt("machines_trademarks.current_amount");
				String trademarkName = rs.getString("dictionary_trademarks.name");
				
				ResultSet rs2 = selectStatement2.executeQuery("select count(*), old_amount, current_amount from automats.messages where automat_name='"+machineName+"' and trademark='"+trademarkName+"'");
				if(rs2.next()){
				if(rs2.getInt("count(*)")==0){
					
					selectStatement2.executeUpdate("Insert into automats.messages (automat_name, automat_address, current_amount, trademark, old_amount) values ('"+machineName+"','"+machineAddress+"',"+currentAmount+",'"+trademarkName+"','"+currentAmount+"');");
					
					
				}else{
					if(rs2.getInt("old_amount")>currentAmount){
					selectStatement2.executeUpdate("Update automats.messages set current_amount="+currentAmount+", is_read=0, old_amount="+currentAmount+" where automat_name='"+machineName+"' and trademark='"+trademarkName+"'");
					
					}else{
						System.out.println("Nie ma zmian");
					
					}
				}
				}
				rs2.close();
			}
			
			rs.close();
			connection.close();
			return "Dane zostały wrzucone do wiadomości";
	
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	public ArrayList<String> getMessageFromDatabase(int userID){
		ArrayList<String>list = new ArrayList<String>();
		try{
			Connection connection = new Connector().getConnection();
			Statement statement = connection.createStatement();
			ResultSet result = statement.executeQuery("SELECT * FROM automats.messages where is_read=0 order by id desc");
			int count = 0;
			while (result.next()) {
				count++;
				String automatName = result.getString("automat_name");
				String automatAddress = result.getString("automat_address");
				String trademark = result.getString("trademark");
				int currentAmount = result.getInt("current_amount");
				int missing = 20-currentAmount;
				
				int messageID=result.getInt("id");
				String output ="<center><table style='width: 500px; height: 150px; '><tr><td colspan='3'><h3>" + count +  ". Wiadomość z automatu: " + automatName + "</h3></td></tr><tr><td style='width: 200px;'><b>Adres Automatu:</b></td><td style='width: 200px;'>" +automatAddress + "</td><td rowspan='2' ><input type='hidden' id='messageID' name='messageID' value='"+messageID+"'/><input type='submit' class='styled-button-12' value='Przeczytałem' onclick='read()' /></td></tr><tr><td style='width: 200px;'><b>" + trademark + ":</b></td><td style='width: 200px;'>Brakuje "+missing + " sztuk</td></tr></table><br/></center>" ;
				list.add(output);
			}
			result.close();
			connection.close();
			
			return list;
			
		}catch(SQLException e){
			e.printStackTrace();
		}
		return null;
	}
	
	public ArrayList<String> setAsRead(int messageID, int userID){
		ArrayList<String>list = new ArrayList<String>();
		try{
			Connection connection = new Connector().getConnection();
			Statement statement = connection.createStatement();
			ResultSet result = statement.executeQuery("SELECT * FROM automats.messages where id="+messageID);
			if(result.next()){
				statement.executeUpdate("Update automats.messages set is_read=1 where id="+messageID);
				for (int i=0;i<getMessageFromDatabase(userID).size();i++){
					list.add(getMessageFromDatabase(userID).get(i));
				}
				result.close();
				connection.close();
				return list;
			}
		}catch(SQLException e){
			e.printStackTrace();
		}
		
		return null;
	}
	
	public String getMessageCount(){
		String message=null;
	
		try{
			Connection connection = new Connector().getConnection();
			Statement statement = connection.createStatement();
			ResultSet result = statement.executeQuery("SELECT count(*) FROM automats.messages where is_read=0");
			if(result.next()){
				int count=result.getInt("count(*)");
				if(count>0){
					if(count==1){
							return "<h3>Masz <b>"+ count + "</b> nową wiadomość o brakach w automatach.</h3>";
					}else if(count==2 || count==3 || count==4){
						return "<h3>Masz <b>"+ count + "</b> nowe wiadomości o brakach w automatach.</h3>";
					}else	
					{
							return "<h3>Masz <b>"+ count + "</b> nowych wiadomości o brakach w automatach.</h3>";
					}
				}else{
					return "<h3>Nie masz nowych wiadomości.</h3>";
				}
			}
			
	
	}catch(SQLException e){
		e.printStackTrace();
	}
		return message;
	}
	
}
